Reconciliation Inventory

This is where you configure reconciliations. The Reconciliation Definition is based on the target accounts being reconciled. After you click Discover, the Reconciliation Inventory is generated. The Reconciliation Inventory is based on the Source Accounts (from the GL) related to the Target Accounts (accounts in the OneStream Cube) that have active Reconciliation Definitions for a specific Workflow Profile.

TIP: The state of each reconciliation inventory item is visible. This helps you to understand which items are already prepared when selecting reconciliations to make edits to.

The Reconciliation Inventory is a grid with a white background. Columns displayed can be rearranged by dragging and dropping them. There is a menu bar at the top with icons to navigate to other pages and an Assigned Match Sets drop-down menu that is a rectangle with a black down arrow. A menu bar at the bottom has options for making changes to items in the grid.

Assigned Match Sets: Select an option to filter the inventory to view where Match Sets are assigned. Select one or more match sets or select one of these options:

  • (Full Inventory): All reconciliations.

  • (All): Reconciliations that have a match set assigned.

  • (None): Reconciliations that do not have a match set assigned.

Edit: Edit the attributes for the selected reconciliation. You can select multiple reconciliations and make changes that apply to all. For example, you can select multiple reconciliations and change the Risk Level from Medium to High.

Delete: Deletes the selected Reconciliation Inventory item. 

NOTE: If the Discover process results in more Reconciliation Inventory items than desired, delete those that are not intended for future processing.

Access: Edit the access group for the selected reconciliation.

Match Set: Manage the assigned match set for the selected reconciliation.

NOTE: Match Set cannot be used to update the assigned match set for a child reconciliation because it is associated with a group. You can manage the match set for child reconciliations in Groups. See Account Group Actions.

Import: Opens a dialog box to select an Excel .xlsx formatted file to import that contains Reconciliation Inventory Items to merge changes. The import will not create new Reconciliation Inventory Items nor allow the [NewGuid] argument. You must close this file before importing it. This button is not accessible by Local Admins.

Export: Exports all reconciliations in the Reconciliation Inventory, except for Account Group type items, as a CSV file that can be opened in Excel. You must add a Named Range starting with the letters xft covering appropriate rows starting with the cell with the word Application in the top left cell. This file should then be saved in Excel .xlsx format. You must close this file before importing. Instructions are included in the exported file. This button is not accessible by Local Admins.

NOTE: The headers at the top indicate the global default value that will be used.

Reconciliation Inventory Item Attributes

Attributes must be configured for every Reconciliation Inventory Item not assigned to an account group. For items assigned to an account group, these attributes are configured at the account group level.

Reconciliation attributes are pulled forward from month to month using Discover. Changes made to reconciliation attributes are reflected in all subsequent periods and do not apply to prior periods.

Editing Reconciliation Attributes

To edit reconciliation attributes:

  1. Select a reconciliation from the inventory list and then click Edit. If multiple reconciliations are selected, the attributes of the first reconciliation selected (from the Inventory page, not the validation grid below) will appear in the dialog box.

    The Edit Reconciliation Attributes dialog box has four sections with blue headings for Information, Role, Action, and Other. Each section includes drop-down menus that are rectangles and black down arrows and text fields that are rectangles. A grid with additional information is listed at the bottom with a blue heading.

  2. Make changes to attributes.

    IMPORTANT: When you edit reconciliation attributes, you must select the check box above the attribute for the change to be applied when you click Save. The Save button displays after a check box is selected.


  3. Click Save.

When editing reconciliation inventory attributes, Security Roles and Notification Methods can be edited at any time. Other attributes can be edited only if the reconciliation has not been prepared.

Required: If clear, this will not be required to be reconciled.  

MC Enabled (Multi-currency Solutions): Indicates if Multi-currency is enabled for the reconciliation.

Within the Reconciliations page, Account and Reporting currency types will appear as a dash and balances will be zero for a reconciliation until the established MC Enabled period.

While Account currency may not be used or required, being able to see Reporting within the Reconciliations page may be helpful. In this instance, where only two currency levels exist (for example, Local and Reporting), it is recommended that Multi-currency be enabled, the Account currency default set to Local, and the MC Enabled setting enabled for each reconciliation.

Performance considerations must also be considered when using Multi-currency for the entire Reconciliation Inventory. Specifically, if the entire inventory enables Multi-currency, forcing a translation on Reconciliation Balances and Detail Items may cause processing times to increase. As such, it is recommended only reconciliations where Multi-currency is necessary be enabled or a translation on demand is run during non-critical business hours. See FX Translation Warning for more information. 

Account Currency (Multi-currency Solutions): Currency type for the Account level balance. This will be a drop-down menu of all currency types within the Currency Filter in the Application Properties. The default currency type will be set to Local and will therefore be the T. Entity’s currency type. This may be changed by selecting a different currency from the drop-down menu.

Reconciling Currency Level (Multi-currency Solutions): Determines the currency level, either Account, Local, or Reporting, to be used for the reconciliation. The default is Local, as this is the level that was previously reconciled prior to Multi-currency was enabled. This reconciling level applies to the Unexplained Limit, Prepare Rules, AutoRec, Balance Change, and Balance Checks. For example, if the Unexplained Limit for a reconciliation is set to 0, the Reconciling Currency Level is set to Account, and the Unexplained Limit is 0 for the Account level but 100 for the Local, the reconciliation could be prepared. However, if the Reconciling Currency is set to Local in the example above, the reconciliation could not be prepared.

The Reconciling Currency level is easily identified as it is the level in larger, bold font.  

Account Group: Shows whether this Reconciliation Inventory Item is related to an account group or (No Group) if it is reconciled individually. If the user is a Local Admin, the list of account groups is limited to only those they manage. The Local Admin can change the assignment of a Reconciliation Inventory Item to a different account group, but once assigned they are unable to set as (No Group).

Custom Attributes: If custom attribute fields were added, this is where they will display in the dialog box. See Attribute Columns.

Preparer: Assign the user or group. The same user or group cannot be added to multiple roles. You can update security roles at any time.

Approver 1-4: Assign the user or group. The same user or group cannot be added to multiple roles. You can update security roles at any time.

Access Group: Assign an Access Group to each Reconciliation Inventory Item that is not assigned to an account group or set to (Unassigned). If user is a Local Admin, list of Access Groups is limited to only those they manage.

Approval Levels: Choose levels of approval required for each Reconciliation Inventory Item from 1 through 4. Ensure that the Access Group assigned has people configured at the appropriate levels of approval. See Access Control.

Notification Method: Select a method for notification created on the Notifications page or set to (Unassigned) .

Risk Level: Options are Low, Medium, or High Risk for this Reconciliation Definition. This is for reporting and other filtering.

Proper Sign: Options are Positive and Negative. Assign a value for the proper signage expected on a reconciled number that is to be imported into the Stage.

Unexplained Limit: This will determine whether a Reconciliation can be prepared if the explained value is within a certain absolute value threshold. By default, a reconciliation is not considered prepared unless the balance is explained to the penny. If the Unexplained Limit is set to 1000 and the currency is USD, then the reconciliation can be prepared if the difference is explained within $1000 USD.

Allow Override (Multi-currency Solutions): When set to Yes, the ability exists to override translated Account, Local, and Reporting amounts for Detail Items. If FX Rates exist for the current period, upon Save (creation) of a Detail Item, OneStream will automatically translate the Account, Local, and Reporting amounts. If Allow Override is enabled, the ability exists to manually input amounts for any of the currency levels. If only one level is overridden, the other translated balances will remain. Similarly, if FX Rates have not been entered for the current period and a level is overridden, the override balance will appear and the amounts to be translated will appear as zero. Amounts that are overridden will hold period over period if a Detail Item is pulled forward. 

NOTE: When an amount is overridden using zero, OneStream automatically retranslates the amount using the rates in FX Rates table. If showing a zero balance for a currency level is required, a new Detail Item must be created with a detail amount of zero and enter the opposite balance for the currency level that needs to be set to zero (that is, offset balance).

Override Support Required: When set to Yes, supporting documentation, either an I-Doc or R-Doc, is required for all detail items with translated amounts that were manually overridden. Note than an S-Doc will also satisfy this requirement.

AutoRec Rule: This is a drop-down list populated from the list of rules created in the AutoRec page. The default is set to (Unassigned), meaning the reconciliation does not have an AutoRec Rule applied.

A second item, (Legacy), will exist in all solutions which first checks the Balance to see if it is zero and if so, will automatically reconcile that reconciliation. Otherwise, it checks the activity in this reconciliation since the last period and compares to the absolute value of the Activity Limit.

Activity Limit: If an AutoRec rule is created that has Activity selected, the rule will check the activity (differences in Balance) in this reconciliation since the last period and compares to the absolute value of this number. For instance, if the balance was explained last month at $1000, the new balance is $100, and the Activity Limit is $500. This reconciliation would not automatically reconcile.

BalCheck Level: Detailed calculation and testing of details from the GL. This is typically used to ensure the value in total matches a summary value as expected. This type of reconciliation is pointed to a Workflow Profile to retrieve data from the Stage as a point of reference for a given reconciliation. The Workflow Profile assigned to check this Balance can be set up in an account group or in a single Reconciliation Inventory Item. The default is set to (Unassigned), which means the reconciliation is a standard reconciliation that does not use Balance Check. When you select (Tracking Level) for a child reconciliation, Balance Check is performed at the tracking level of the child reconciliation and the BalCheck WF Profile selection is used. When you select (Tracking Level) for an account group, the BalCheck WF Profile selection of the account group is ignored and will be used from the child reconciliation.

BalCheck WF Profile: This is required if using a Reconciliation Type of Bal-Check. Details from the GL are loaded to Stage in a separate Workflow Profile and tested by some form of Transformation Rule such as mapping many detail lines to one summarized value or even using complex Transformation Event Handler logic to calculate a check figure. For example, the Transformation Rule might add all of the detailed transactions that make up a Trade Receivables Account and compare it to the amount imported from the Trial Balance. If that matches when the number is brought into the Reconciliation as an explained amount, it may reconcile or not based on the amount. This field is where the Workflow Profile containing that detailed data load is specified. After a Workflow Profile is assigned here for a Balance Check-type reconciliation, Account Reconciliations checks to see if that Transformation Rule passed or failed to determine whether this reconciliation is complete.

NOTE: The reconciliation balance for RCM is based on the source value loaded. Although the source value may be transformed to flip signs for consolidation purposes, the flip sign will not apply when the reconciliation is created in RCM. Contrary to this, when uploading Balance Check information in the reconciliation workflow, the flip sign is available to transform the source Balance Check values. This may be needed to ensure that the sign aligns with the reconciliation balance.

Multi-currency Solutions: Loading in Detail Amounts, Detail Currency Types, Account level, and Reporting level balances requires identifying the columns related to these items within the Data Source. Note that Detail Amount represents the transaction amount, which could be in a currency type that is different than any reconciliation currency level and is different than what is loaded for Single Currency solutions since they just load Local currency balances. Detail Amounts, Account Amounts, and Reporting Amounts need to be set to the Data Type of Attribute Value and Detail Currency needs to be set to the Data Type of Attribute. Local amounts must exist for BalCheck to properly translate and calculate. If null, values will not translate. As such, ensure null values are replaced with zero.

Allow Auto Pull Forward: When set to Yes, the reconciliation automatically pulls forward I-Items and T-Items with the related I-Docs during processing when the account balance is first created. T-Items are also converted into I-Items.

Prepare Rule: Overrides the Unexplained Limit. Rule logic can include any item from the Substitution Variable Selector. See Expression Rule Syntax.

Example 1:
|Balance| < 1000

Example 2:
|BalanceAccount| < 1000

Example 3:

This logic will set a global unexplained limit based on currency by retrieving the closing FX rate for the individual reconciliation’s currency relative to USD. 

 |UnexplainedBalance| < XFBR(MyCustomBusinessRule,UnexplainedLimitHelper,Currency=|Currency|,Time=|Wtk|)

Substitution Variable Selector: A list of Substitution Variables used in Prepare Rule as an input to an expression. Select one of these Substitution Variables and click the blue field next to it. Type Ctrl-C and it will copy that text to the Windows clipboard for convenient pasting later. The options are either variables from Account Reconciliations or fields in the Stage tables. For example, |Ac| is the Account field from Stage while |AcT| is the post translated Account from that same table.

Preparer Workday Due: Enter + or - and a 1–2 digit number for the days. For example, if you enter -10 and the close date is 1/31/22, then the preparer due is 1/21/22.

Approver Workday Due: Indicates the due date for the final approver. Enter + or - and a 1–2 digit number for the days. For example, if you enter +2 and the close date is 1/31/22, then the approver due date is 2/2/22.

IMPORTANT: The Approver date cannot be before Preparer date.

Frequency: Determines how often the completion of this Reconciliation Definition is required. The default is 1–12, which indicates months 1–12. This can be 3, 6, 9, 12 if required quarterly, or you can enter another type of frequency expression.

NOTE: If the Frequency is changed on the Reconciliation Definition, some reconciliations may be Prepared or still In Process. This would normally stop the user from preparing them due to this new Frequency. In these cases, the reconciliations will be marked with a Status of Frequency Changed, which will not prevent the preparer from preparing. This status is ignored in all status counts except for the Scorecard Preparation Status chart.

Template (XLSX): The Reconciliation Definition sets the default Excel Reconciliation Template for any related Reconciliation Inventory Items, but a single Reconciliation Inventory Item can override this template assignment with this field.

WF Profile: This is the Workflow Profile that was discovered for this Source Account and is a Base Input Child Import Workflow Profile.

Recon Scenario: This will default to the Recon Scenario set in Global Options.

T.Account: Target Account in the Cube and this comes from the Reconciliation Definition.

S.Account: Source Account. This comes from the related Stage area from the Source Scenario. The Source Accounts are derived from that Workflow Profile's Transformation Rules and are based on the Target Account in the Reconciliation Definition.

S.Account Desc.: Source Account Description. This is based on the option selected in the Source Account Description Dimension field in Settings > Global Setup > Global Options.

T.Entity: Target Entity in the Cube. There will be an entry here for each Entity that has relevant data in the Stage in any time period in the application from the Source Scenario.

S.Entity: Source Entity from the Stage.

Currency (Single Currency Solutions): Currency is the Target Entity currency per the Entity dimension.

Local Currency (Multi-currency Solutions): Currency type for the Local level balance. This is the same as the Currency column in Single Currency Solutions. It is not editable within Account Reconciliations as it is maintained within the Entity dimension.

Reporting Currency (Multi-currency Solutions): Currency type for the Reporting level balance. This is not editable as it is derived from the Cube currency. Only a single Reporting currency is allowed per Cube.

Discovery Time: The date and time when an account group was created or when a Reconciliation Inventory Item was first discovered.

NOTE: The filter on this column can be used to find newly discovered Reconciliation Inventory Items by, for instance, setting the filter properties to be Is Greater Than and a date that is before Discovery is done for a given month, such as the first day.

Expression Rule Syntax

The fields for Prepare Rule and AutoRec Rule use the same expression syntax. OneStream uses an ADO.NET data table calculated column to interpret expressions, so there is no OneStream parser logic involved. Choices from the Substitution Variable Selectors can be used to make these more dynamic. The expression evaluator supports the following operators and more:

  • And, Not, In, Between, Like, Null, Or, Trim

  • Open bracket ‘(‘ and close bracket ‘)’

  • <, >, <=, >=, <>, =

  • + (addition)

  • - (subtraction)

  • * (multiplication)

  • / (division)

  • % (modulus)

  • Conditional ‘if’

  • Substring

Example:
|BalanceChange| > 1000 And |BalanceLocal| = 0

XFBR String Business Rules can also be used to determine the expression to be placed here at run time. For XFBR String rule syntax, refer to the Dashboard XFBRString section of the Design and Reference Guide.